﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using EShop.Model;
using System.Data.Sql;

using System.Data.SqlClient;
using EShop.Common;

namespace EShop.DAL
{
    public class EmployeeDAL : BaseDAL
    {

        /// <summary>
        /// 增加一个新的员工，增加成功后返回新添加员工的信息
        /// JF
        /// </summary>
        /// <param name="employeeInfo"></param>
        /// <returns></returns>
        public DataTable AppendEmployeeInfo(EmployeeInfo employeeInfo)
        {
            string strUSP = "USP_Add_EmployeeInfo";

            try
            {
                SqlParameter[] parameters = {
                                                new SqlParameter("@FullName", System.Data.SqlDbType.VarChar, 50),
                                                new SqlParameter("@EmployeePID", System.Data.SqlDbType.VarChar, 18),
                                                new SqlParameter("@Sex", System.Data.SqlDbType.Bit),
                                                new SqlParameter("@Brithday", System.Data.SqlDbType.DateTime),
                                                new SqlParameter("@IsGregorianCalendar", System.Data.SqlDbType.Bit),
                                                new SqlParameter("@Popedom", System.Data.SqlDbType.VarChar, 50),
                                                new SqlParameter("@Address", System.Data.SqlDbType.VarChar,255),
                                                new SqlParameter("@Mobile", System.Data.SqlDbType.VarChar, 12),
                                                new SqlParameter("@Remark", System.Data.SqlDbType.Text)
                                            };
                parameters[0].Value = employeeInfo.FullName;
                parameters[1].Value = employeeInfo.EmployeePID;
                parameters[2].Value = employeeInfo.Sex;
                parameters[3].Value = employeeInfo.Brithday;
                parameters[4].Value = employeeInfo.IsGregorianCalendar;
                parameters[5].Value = employeeInfo.Popedom;
                parameters[6].Value = employeeInfo.Address;
                parameters[7].Value = employeeInfo.Mobile;
                parameters[8].Value = employeeInfo.Remark;

                return DbHelper.ExecuteDataSetSQL(strUSP, CommandType.StoredProcedure, parameters).Tables[0];

            }
            catch (Exception ex)
            {
                log.Write((string.Format("USP: {0}, EmployeeInfo: {1},\r\n ex : {2}", strUSP, employeeInfo.ToString(), ex.Message)));
                throw new Exception(string.Format("USP: {0}, EmployeeInfo: {1},\r\n ex : {2}", strUSP, employeeInfo.ToString(),ex.Message));
            }
        }

        /// <summary>
        /// 修改员工信息
        /// </summary>
        /// <param name="oEmployeeInfo"></param>
        /// <returns></returns>
        public bool UpdateEmployeeInfo(EmployeeInfo oEmployeeInfo)
        {
            string strUSP = "USP_EmployeeInfo_Update";
            try
            {
                SqlParameter[] parameters ={
                                           new SqlParameter("@EmployeeID",SqlDbType.VarChar,12),
                                           new SqlParameter("@FullName",SqlDbType.VarChar,50),
                                           new SqlParameter("@Sex",SqlDbType.Bit),
                                           new SqlParameter("@EmployeePID",SqlDbType.VarChar,18),
                                           new SqlParameter("@Brithday",SqlDbType.DateTime),
                                           new SqlParameter("@IsGregorianCalendar",SqlDbType.Bit),
                                           new SqlParameter("@Popedom",SqlDbType.VarChar,50),
                                           new SqlParameter("@Address",SqlDbType.VarChar,255),
                                           new SqlParameter("@Mobile",SqlDbType.VarChar,12),
                                           new SqlParameter("@Remark",SqlDbType.Text),
                                      };
                parameters[0].Value = oEmployeeInfo.EmployeeID;
                parameters[1].Value = oEmployeeInfo.FullName;
                parameters[2].Value = oEmployeeInfo.Sex;
                parameters[3].Value = oEmployeeInfo.EmployeePID;
                parameters[4].Value = oEmployeeInfo.Brithday;
                parameters[5].Value = oEmployeeInfo.IsGregorianCalendar;
                parameters[6].Value = oEmployeeInfo.Popedom;
                parameters[7].Value = oEmployeeInfo.Address;
                parameters[8].Value = oEmployeeInfo.Mobile;
                parameters[9].Value = oEmployeeInfo.Remark;
                if (DbHelper.ExecuteNonQuerySQL(strUSP, CommandType.StoredProcedure, parameters) > 0)
                    return true;
                else return false;
            }
            catch(Exception e)
            {
                log.Write((string.Format("USP: {0}, EmployeeInfo: {1},\r\n ex : {2}", strUSP, oEmployeeInfo.ToString(), e.Message)));
                throw;
            }
        }

        /// <summary>
        /// 员工登录
        /// 登录成功后，获取该员工的信息
        /// 更新最后登录时间
        /// </summary>
        /// <param name="strEmployeeID">工号</param>
        /// <param name="strEmployeePWD">密码</param>
        /// <returns></returns>
        public EmployeeInfo EmployeeLogined(string strEmployeeID, string strEmployeePWD)
        {
            string strUSP = "USP_EmployeeLogin";
            EmployeeInfo oEmployeeInfo = null;

            try
            {
                SqlParameter[] parameters = {
                                                new SqlParameter("@EmployeeID", SqlDbType.VarChar, 12),
                                                new SqlParameter("@EmployeePWD", SqlDbType.VarChar, 50)
                                            };
                parameters[0].Value = strEmployeeID;
                parameters[1].Value = strEmployeePWD;

                SqlDataReader reader = (SqlDataReader)DbHelper.ExecuteReaderSQL(strUSP, CommandType.StoredProcedure, parameters);

                if (reader.Read())
                {
                    oEmployeeInfo = new EmployeeInfo();
                    oEmployeeInfo.ID = reader["ID"].ToString();
                    oEmployeeInfo.EmployeeID = reader["EmployeeID"].ToString();
                    oEmployeeInfo.FullName = reader["FullName"].ToString();
                    oEmployeeInfo.EmployeePermission = Common.SystemLibrary.IntConvertToPermission(reader["EmployeePurview"].ToString());
                    if (!reader.IsDBNull(reader.GetOrdinal("LastLoginDatetime")))
                        oEmployeeInfo.LastLoginDatatime = reader.GetDateTime(reader.GetOrdinal("LastLoginDatetime"));
                }
                reader.Close();

                return oEmployeeInfo;
            }
            catch (Exception ex)
            {
                log.Write((string.Format("USP: {0}, \r\n ex : {2}", strUSP,ex.Message)));
                throw new Exception(string.Format("USP: {0}, EmployeeID: {1}, EmployeePWD: {2}, ex: {3}", strUSP, strEmployeeID, strEmployeePWD, ex.Message));
            }
        }

        /// <summary>
        /// 取所有员工的权限数据
        /// </summary>
        /// <returns></returns>
        public List<EmployeeInfo> GetEmployeePermission()
        {
            string strUSP = "USP_GetAllEmployeePermission";
            List<EmployeeInfo> aEmployeePermissionList = new List<EmployeeInfo>();


            try
            {
                SqlDataReader reader = (SqlDataReader)DbHelper.ExecuteReaderSQL(strUSP, CommandType.StoredProcedure);
                while (reader.Read())
                {
                    EmployeeInfo oInfo = new EmployeeInfo();
                    oInfo.ID = reader["ID"].ToString();
                    oInfo.EmployeeID = reader["EmployeeID"].ToString();
                    oInfo.FullName = reader["FullName"].ToString();
                    oInfo.EmployeePermission = Common.SystemLibrary.IntConvertToPermission(reader["EmployeePurview"].ToString());
                    aEmployeePermissionList.Add(oInfo);
                }
                reader.Close();

                return aEmployeePermissionList;
            }
            catch (Exception ex)
            {
                log.Write((string.Format("USP: {0}, \r\n ex : {1}", strUSP, ex.Message)));
                throw new Exception(string.Format(" USP : {0}\r\n ex:", strUSP, ex.Message));
            }
        }

        /// <summary>
        /// 修改相应员工的权限
        /// </summary>
        /// <param name="strEID">EmployeeID </param>
        /// <param name="iPermission"> 权限值</param>
        /// <returns></returns>
        public bool UpdateEmployeePermission(string strEID, int iPermission)
        {
            string strUSP = "USP_UpdateEmployeePermission";
            try
            {
                SqlParameter[] parameters = {
                                                new SqlParameter("@EmployeeID", strEID),
                                                new SqlParameter("@EmployeePermission", iPermission)
                                            };
                object obj = DbHelper.ExecuteNonQuerySQL(strUSP, CommandType.StoredProcedure, parameters);
                int iResult = 0;
                int.TryParse(obj.ToString(), out iResult);
                if (iResult > 0)
                    return true;

                return false;
            }
            catch (Exception ex)
            {
                log.Write((string.Format("USP: {0}, \r\n ex : {1}", strUSP, ex.Message)));
                throw new Exception(string.Format("USP: {0}, EmployeeID: {1}, PermissionValue:{2} \r\n ex: {3} ", strUSP, strEID, iPermission, ex.Message));
            }
        }

        /// <summary>
        /// 获取所有[指定工号]的员工信息
        /// </summary>
        /// <param name="strEmployeeID"></param>
        /// <param name="iBeginIndex"></param>
        /// <param name="iLength"></param>
        /// <returns></returns>
        public DataTable QueryEmployeeInfo(string strEmployeeID)
        {
            string strUSP = "USP_EmployeeInfo_Query";
            try
            {
                SqlParameter parameter = null;
                parameter = new SqlParameter("@EmployeeID", SqlDbType.VarChar, 12);
                if (!string.IsNullOrEmpty(strEmployeeID))
                {
                    parameter.Value = strEmployeeID;
                    return DbHelper.ExecuteDataSetSQL(strUSP, CommandType.StoredProcedure, parameter).Tables[0];
                }
                else
                {
                    return DbHelper.ExecuteDataSetSQL(strUSP, CommandType.StoredProcedure).Tables[0];
                }
            }
            catch(Exception ex)
            {
                log.Write((string.Format("USP: {0}, \r\n ex : {1}", strUSP, ex.Message)));
                throw;
            }
        }

        /// <summary>
        ///根据关键字，查询员工
        /// </summary>
        /// <param name="keyword"></param>
        /// <param name="type"></param>
        /// <returns></returns>
        public DataTable SearchEmployee(string strKeyword, int type)
        {
            try
            {
                string sql = string.Empty;
                sql = "SELECT EmployeeID,FullName,Sex,Brithday,IsGregorianCalendar,LastLoginDatetime FROM T_Employee_Info WHERE ";
                SqlParameter parameter = null;
                switch (type)
                {
                    case 0://工号
                        sql += "EmployeeID LIKE ";
                        strKeyword = "%" + strKeyword + "%";
                        parameter = new SqlParameter("@Key", SqlDbType.VarChar, 12);
                        break;
                    case 1://姓名
                        sql += "FullName LIKE ";
                        strKeyword = "%" + strKeyword + "%";
                        parameter = new SqlParameter("@Key", SqlDbType.VarChar, 50);
                        break;
                    case 2://性别
                        sql += "Sex = ";
                        strKeyword = strKeyword == "男" ? "0" : "1";
                        parameter = new SqlParameter("@Key", SqlDbType.Bit);
                        break;
                    case 3://生日
                        sql += "Birthday = ";
                        parameter = new SqlParameter("@Key", SqlDbType.Date);
                        break;
                    case 4://身份证
                        sql += "EmployeePID LIKE ";
                        strKeyword = "%" + strKeyword + "%";
                        parameter = new SqlParameter("@Key", SqlDbType.VarChar, 18);
                        break;
                    case 5://联系地址
                        sql += "[Address] LIKE ";
                        strKeyword = "%" + strKeyword + "%";
                        parameter = new SqlParameter("@Key", SqlDbType.Text);
                        break;
                    case 6://手机号码
                        sql += "Mobile LIKE ";
                        strKeyword = "%" + strKeyword + "%";
                        parameter = new SqlParameter("@Key", SqlDbType.VarChar, 12);
                        break;
                    case 7://权限
                    default:
                        sql += "[EmployeePurview] = ";
                        parameter = new SqlParameter("@Key", SqlDbType.Int);
                        break;
                }
                sql += " @Key";
                parameter.Value = strKeyword;
                return DbHelper.ExecuteDataSetSQL(sql, 0, 0, CommandType.Text, parameter).Tables[0];
            }
            catch(Exception ex)
            {
                log.Write((string.Format(" \r\n ex : {0}",  ex.Message)));
                throw;
            }
        }

        /// <summary>
        /// 修改密码
        /// </summary>
        /// <param name="strEmployeeID"></param>
        /// <param name="strPassword"></param>
        /// <returns></returns>
        public bool ChangePassword(string strEmployeeID, string strPassword)
        {
            try
            {
                string sql = "UPDATE T_Employee_Info SET EmployeePWD='" + strPassword + "' WHERE EmployeeID='" + strEmployeeID + "'";
                return DbHelper.ExecuteNonQuerySQL(sql) > 0 ? true : false;
            }
            catch(Exception ex)
            {
                log.Write((string.Format("ex : {0}", ex.Message)));
                throw;
            }
        }
    }
}

